﻿
using System.Data;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace DataObject.QA
{
    internal class QADAL : DAL
    {
        /***************************************
        = Back End
        -------------------------------------- */
        internal static int QA_Create(QAObject o)
        {
            return (int)SqlHelper.ExecuteScalar(Conn, "QA_Create", o.QA_Text
                                                                  , o.QA_IsFeature
                                                                  , o.QA_IsHighlight
                                                                  , o.QA_IsActive
                                                                  , o.QA_Name
                                                                  , o.QA_Email
                                                                  , o.QA_ReplyTo);
        }

        internal static void QA_Update(QAObject o)
        {
            SqlHelper.ExecuteNonQuery(Conn, "QA_Update" , o.QA_ID
                                                        , o.QA_Text
                                                        , o.QA_IsFeature
                                                        , o.QA_IsHighlight
                                                        , o.QA_IsActive
                                                        , o.QA_Name
                                                        , o.QA_Email
                                                        , o.QA_ReplyTo);
        }

        internal static void QA_Delete(int id)
        {
            SqlHelper.ExecuteNonQuery(Conn, "QA_Delete", id);
        }

        internal static void QA_Answer_DeleteAll(int id)
        {
            SqlHelper.ExecuteNonQuery(Conn, "QA_Answer_DeleteAll", id);
        }

        internal static void Question_Update_Status(int id, bool isActive)
        {
            SqlHelper.ExecuteNonQuery(Conn, "QA_Update_Status", id, isActive);
        }

        internal static IDataReader QA_GetById(int id)
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT TOP 1 * ");
            sb.AppendFormat("FROM [{0}]", Constants.Table_QA.TableName);
            sb.AppendFormat("WHERE [{0}] = {1}", Constants.Table_QA.QA_ID, id);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static int Question_GetStatistic(int isActive)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT([{0}]) ", Constants.Table_QA.QA_ID);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_QA.TableName);
            sb.AppendFormat("WHERE [{0}] = {1} AND ([{2}] IS NULL OR [{2}] = 0) ", Constants.Table_QA.QA_IsActive, isActive, Constants.Table_QA.QA_ReplyTo);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Question_GetListByTypeByStatusPaging(int isActive, int pageIndex, int pageSize)
        {
            sb = new StringBuilder();
            sb.AppendLine("DECLARE @StartRow INT");
            sb.AppendFormat("SET @StartRow = ({0} - 1) * {1} + 1 ", pageIndex, pageSize);
            sb.AppendLine("DECLARE @EndRow INT");
            sb.AppendFormat("SET @EndRow = {0} * {1}; ", pageIndex, pageSize);
            sb.AppendLine("WITH temp AS");
            sb.AppendLine("(");
            sb.AppendLine("SELECT TOP(@EndRow) * ");
            sb.AppendFormat(", ROW_NUMBER() OVER (ORDER BY [{0}] DESC) AS 'RowNumber' ", Constants.Table_QA.QA_CreatedDate);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_QA.TableName);
            sb.AppendFormat("WHERE [{0}] = {1} AND ([{2}] IS NULL OR [{2}] = 0) ", Constants.Table_QA.QA_IsActive, isActive, Constants.Table_QA.QA_ReplyTo);
            sb.AppendLine(")");
            sb.AppendLine("SELECT * FROM temp ");
            sb.AppendLine("WHERE RowNumber BETWEEN @StartRow AND @EndRow");
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Answer_GetListByReplyTo(int id)
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT * ");
            sb.AppendFormat("FROM [{0}]", Constants.Table_QA.TableName);
            sb.AppendFormat("WHERE [{0}] = {1}", Constants.Table_QA.QA_ReplyTo, id);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }
    }
}
